package com.example.project.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper{
	private static final String DB_NAME = "sql.db"; 	// 저장될 DB 이름
	private static final int 	DB_VER = 1;	// DB 버전
	private static DBHelper dbHelper;
	
	public DBHelper(Context context,  String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}
	
	public static DBHelper getInstance(Context context) {
		if (dbHelper == null) {
			dbHelper = new DBHelper(context, DB_NAME, null,
					DB_VER);
		}
		return dbHelper;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		//테이블 생성 및 초기 작업 
		String sql = "CREATE TABLE sql ("
				+ "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "list_no     CHAR(5)      NOT NULL,"
				+ "query       VARCHAR(255) NOT NULL,"
				+ "category    VARCHAR(255) NOT NULL,"
				+ "mysql       INTEGER      NOT NULL DEFAULT 0,"
				+ "oracle      INTEGER      NOT NULL DEFAULT 0,"
				+ "sqlserver   INTEGER      NOT NULL DEFAULT 0,"
				+ "description TEXT         NULL,"
				+ "UNIQUE(list_no, query))";

				
			/*	"create table tb_score(" +
			"code integer primary key autoincrement," +
			"name text not null, score integer not null)";*/
		db.execSQL(sql); 
		
		db.execSQL("insert into sql values(null, '00000', 'create', '테이블생성', 1, 1, 1, "
				+ "'create table 테이블이름"
				+ "\n\t(칼럼명1 DATATYPE [DEFAULT 형식],"
				+ "\n\t  칼럼명2 DATATYPE [DEFAULT 형식],"
				+ "\n\t  ... ,"
				+ "\n\t  칼럼명n DATATYPE [DEFAULT 형식]);"
				+ "\n\t제약조건이 있을 경우 CONSTRAINT를 이용하여 추가"
				+ "\n\t제약조건의 종류: PRIMARY KEY, UNIQUE KEY, NOT NULL, CHECK, FOREIGN KEY');");
		
		db.execSQL("insert into sql values(null, '00001', 'alter', '테이블구조변경', 1, 1, 1, "
				+ "'ADD COLUMN:\talter table 테이블명 "
				+ "\n\tadd (추가할 칼럼명 DATATYPE);"
				+ "\n\n\tDROP COLUMN:\talter table 테이블명"
				+ "\n\t drop column 삭제할 칼럼명;"
				+ "\n\n\tMODIFY COLUMN:\talter table 테이블명 modify "
				+ "\n\t (수정할 칼럼명1 DATATYPE [DEFAULT 형식], "
				+ "\n\t 수정할 칼럼명2 DATATYPE [DEFAULT 형식], "
				+ "\n\t ..., 수정할 칼럼명n DATATYPE [DEFAULT 형식]);');");
		
		db.execSQL("insert into sql values(null, '00001', 'alter테이블명constraint', '테이블구조변경', 0, 1, 1, "
				+ "'DROP CONSTRAINT:\talter table 테이블명 \n\tdrop constraint 제약조건명;"
				+ "\n\n\tADD CONSTRAINT:\talter table 테이블명 \n\tadd constraint 제약조건명 제약조건 (칼럼명);');");
		
		db.execSQL("insert into sql values(null, '00002', 'rename', '테이블이름변경', 0, 1, 0, "
				+ "'rename 변경전 테이블명\n\t to 변경후 테이블명;');");
		db.execSQL("insert into sql values(null, '00002', 'sp_rename', '테이블이름변경', 0, 0, 1, "
				+ "'sp_rename 변경전 테이블명,\n\t 변경후 테이블명;');");
		db.execSQL("insert into sql values(null, '00002', 'alter테이블명rename', '테이블이름변경', 1, 0, 0, "
				+ "'alter table 테이블명\n\t rename 변경할 테이블명;');");
		
		db.execSQL("insert into sql values(null, '00003', 'drop', '테이블삭제', 1, 1, 1, "
				+ "'drop table 테이블명;');");
		
		db.execSQL("insert into sql values(null, '00004', 'truncate', '테이블행제거', 1, 1, 1, "
				+ "'truncate table 테이블명;"
				+ "\n\t*drop table은 테이블 자제가 없어지고"
				+ "\n\t truncate는 테이블은 유지하고 데이터만 삭제"
				+ "\n\t delete와의 차이는 auto commit과 내부 처리 방식');");
		
		
		
		db.execSQL("insert into sql values(null, '10000', 'insert', '데이터입력', 1, 1, 1, "
				+ "'insert into 테이블명(COLUMN_LIST)"
				+ "\n\t values(COLUMN_LIST에 넣을 VALUE_LIST)"
				+ "\n\n\t insert into 테이블명"
				+ "\n\t values(전체 COLUMN에 넣을 VALUE_LIST)');");
		
		/*db.execSQL("insert into sql values(null, '10001', 'select', '데이터조회', 1, 1, 1, "
				+ "'select [all/distinct] 보고 싶은 칼럼명1,"
				+ "\n\t ..., 보고 싶은 칼럼명n"
				+ "\n\t from 테이블명;"
				+ "\n\t*all: default옵션, 중복된 데이터가 있어도 모두 출력"
				+ "\n\t distinct: 중복된 데이터가 있는 경우 1건으로 출력');");*/
		
		db.execSQL("insert into sql values(null, '10002', 'update', '데이터수정', 1, 1, 1, "
				+ "'update 테이블명 "
				+ "\n\t set 수정되어야 할 칼럼명=수정되기를 원하는 새로운 값;"
				+ "\n\t*where절을 사용하지 않으면 칼럼의 모든 값이 변경');");
		
		db.execSQL("insert into sql values(null, '10003', 'delete', '데이터삭제', 1, 1, 1, "
				+ "'delete from 테이블명;"
				+ "\n\t *where절을 사용하지 않으면 테이블의 전체 데이터가 삭제');");
		
		
		
		db.execSQL("insert into sql values(null, '20000', 'commit', '데이터저장', 1, 1, 0, "
				+ "'commit;"
				+ "\n\t*데이터에 대한 변경 사항들이 데이터베이스에 반영"
				+ "\n\t commit이전에는 메모리 버퍼에만 영향을 받았기에"
				+ "\n\t  데이터의 변경 이전 상태로 복구 가능"
				+ "\n\t 이전 데이터는 영원히 잃어버리게 된다"
				+ "\n\t 모든 사용자는 결과를 볼 수 있다"
				+ "\n\t*SQL Server의 경우 기본적으로 auto_commit모드');");
		db.execSQL("insert into sql values(null, '20001', 'rollback', '데이터복구', 1, 1, 1, "
				+ "'rollback [to savepoint명];"
				+ "\n\t*commit(혹은 savepoint)이전 상태로 복구"
				+ "\n\t*SQL Server의 경우 데이터 변경 사항 취소, 이전 데이터 다시 재저장');");
		db.execSQL("insert into sql values(null, '20002', 'savepoint', '복구지점', 1, 1, 0, "
				+ "'savepoint 저장점명;');");
		db.execSQL("insert into sql values(null, '20002', 'savetransaction', '복구지점', 0, 0, 1, "
				+ "'save transaction 저장점명;');");
		
		db.execSQL("insert into sql values(null, '30000', 'create user', '유저생성', 1, 1, 0, "
				+ "'create user 유저명 identified by 비밀번호;"
				+ "\n\t*유저를 생성할 권한이 있는 유저로 로그인시 생성 가능');");
		
		db.execSQL("insert into sql values(null, '30000', 'create user login', '유저생성', 0, 0, 1, "
				+ "'create user 유저명 [{for|from} login 로그인명]"
				+ "\n\t [with default_schema = 스키마명 ];"
				+ "\n\t*생성하고자 하는 유저가 속할 데이터베이스로 이동후 처리');");
		
		db.execSQL("insert into sql values(null, '30001', 'grant', '권한부여', 0, 1, 1, "
				+ "'grant 권한종류 to 유저명;');");
		
		db.execSQL("insert into sql values(null, '30001', 'grant on', '권한부여', 1, 0, 0, "
				+ "'grant 권한종류 on 데이터베이스명 to 유저명;');");
		
		db.execSQL("insert into sql values(null, '30002', 'role', '권한집합', 0, 1, 1, "
				+ "'ROLE 생성: create role 롤명;"
				+ "\n\n\tROLE에 권한 부여: grant 권한종류 to 롤명"
				+ "\n\n\tROLE 사용: grant 롤명 to 롤에 부여된 권한을 줄 유저명');");
		
		db.execSQL("insert into sql values(null, '30003', 'revoke', '권한취소', 1, 1, 1, "
				+ "'revoke 권한종류 from 유저명');");
		
		
		db.execSQL("insert into sql values(null, '60000', 'union', '합집합', 0, 1, 0, "
				+ "'select 칼럼명 from 테이블명 "
				+ "\n\t [where, group by, having]"
				+ "\n\tunion"
				+ "\n\tselect 칼럼명 from 테이블명 "
				+ "\n\t [where, group by, having];"
				+ "\n\t*두 질의에 대한 연산 결과 중 중복된 건을 1건으로 처리하여 나타냄');");
		
		db.execSQL("insert into sql values(null, '60000', 'union all', '합집합', 0, 0, 1, "
				+ "'select 칼럼명 from 테이블명 "
				+ "\n\t [where, group by, having]"
				+ "\n\tunion all"
				+ "\n\tselect 칼럼명 from 테이블명 "
				+ "\n\t [where, group by, having];"
				+ "\n\t*두 질의에 대한 연산 결과를 중복에 대한 배제 없이 나타냄"
				+ "\n\t 두 개의 결과 집합을 단순히 합친 것과 동일');");
		
		db.execSQL("insert into sql values(null, '60001', 'intersect', '교집합', 0, 1, 0, "
				+ "'select 칼럼명 from 테이블명 "
				+ "\n\t [where, group by, having]"
				+ "\n\tintersect"
				+ "\n\tselect 칼럼명 from 테이블명 "
				+ "\n\t [where, group by, having];"
				+ "\n\t*두 집합의 교집합"
				+ "\n\t AND연산자 EXIST, IN 서브쿼리를 이용하여 동일한 결과 출력 가능 ');");
		
		db.execSQL("insert into sql values(null, '60002', 'minus', '차집합', 0, 1, 0, '"
				+ "select 칼럼명 from 테이블명 \n\t [where, group by, having]"
				+ "\n\tminus"
				+ "\n\tselect 칼럼명 from 테이블명 \n\t [where, group by, having];"
				+ "\n\t*앞 집합-뒤 집합의 결과(차집합)"
				+ "\n\t NOT EXIST, NOT IN 서브쿼리로 변경 가능');");
		
		
		db.execSQL("insert into sql values(null, '60002', 'except', '차집합', 0, 0, 1, '"
				+ "select 칼럼명 from 테이블명 \n\t [where, group by, having]"
				+ "\n\texcept"
				+ "\n\tselect 칼럼명 from 테이블명 \n\t [where, group by, having];"
				+ "\n\t*앞 집합-뒤 집합의 결과(차집합)"
				+ "\n\t NOT EXIST, NOT IN 서브쿼리로 변경 가능');");
		
		sql = "CREATE TABLE historysql ("
				+ "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "list_no     CHAR(5)      NOT NULL,"				
				+ "UNIQUE(list_no))";

		db.execSQL(sql); 
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//DB의 버전이 바뀌면 수행
		db.execSQL("drop table if exist sql");
		onCreate(db);
	}
	
	public Cursor selectCategory() {//카테고리 distinct로 추출
		// (distinct, table, columns, selection, selectionArgs, groupBy, having,
		// orderBy, limit)
		Cursor cursor = getWritableDatabase().query(true, "sql", new String[] { "list_no",
				"category" }, null, null, "list_no", null, null, null);
		return cursor;
	}

	public Cursor select(String list_no) {//list_no로 검색해서 description
		String sql = "SELECT mysql, oracle, sqlserver, query, description FROM sql WHERE list_no='"+list_no+"'";
		Cursor cursor = getWritableDatabase().rawQuery(sql,null);
		return cursor;//description은 쿼리마다 하나씩이므로 1개 이상이 될 수 있다
	}
	
	public Cursor search(String searchkey) {//list_no로 검색해서 description
		String sql = "SELECT list_no, category FROM sql WHERE query like '%"+searchkey+"%' group by list_no";
		Cursor cursor = getWritableDatabase().rawQuery(sql,null);
		return cursor;//description은 쿼리마다 하나씩이므로 1개 이상이 될 수 있다
	}
	
	public Cursor searchdesc(String list_no, String query) {//list_no로 검색해서 description\
		String sql = "SELECT description FROM sql WHERE list_no='"+list_no+"' and query='" + query + "'";
		Log.d("PRINT", sql);
		Cursor cursor = getWritableDatabase().rawQuery(sql,null);
		return cursor;
	}

	/*
	 * public int update(ContentValues updateRowValue, String whereClause,
	 * String[] whereArgs) { return getWritableDatabase().update(TABLE_DIC,
	 * updateRowValue, whereClause, whereArgs); }
	 */
	public long update(String query, String description) {
		ContentValues values = new ContentValues();
		values.put("description", description);
		Log.d("print", "update하러들어왔다");
		long result = getWritableDatabase().update("sql", values, "query = ?",
				new String[] {query});
		return result;
	}
	/*public void update(String query, String description) {
		SQLiteDatabase db = getWritableDatabase();
		String sql = "update sql set description='"+description+"' where query='"+query+"'";
		Log.d("print", sql);
		db.execSQL(sql);
	}*/
	
	public long insert(String category, String query, String description, int biglevel, int[] arr) {
		ContentValues values = new ContentValues();
		String list_no = existCategory(category);
		if(list_no == null){//기존에 있던 카테고리가 아니라면.................!!!!!!
			list_no = makeList_no(Integer.toString(biglevel));
			values.put("list_no", list_no);
			Log.d("PRINT", "list_no: "+list_no);
		}
		values.put("list_no", list_no);
		values.put("query", query);
		values.put("category", category);
		values.put("mysql", arr[0]);
		values.put("oracle", arr[1]);
		values.put("sqlserver", arr[2]);
		values.put("description", description);
		//(table, nullColumnHack, values)
		long result = getWritableDatabase().insert("sql", null, values);
		Log.d("print", result+"");
		return result;
	}
	
	public boolean existQuery(String query) {//추가할 쿼리값을 가지고 와서 추가가능한지 확인
		String sql = "SELECT * FROM sql WHERE query like '"+query+"'";
		Log.d("print", sql);
		Cursor cursor = getWritableDatabase().rawQuery(sql,null);

		return cursor.getCount() > 0;//1개 이상의 결과값이 나온다는 말은 그 쿼리가 이미 존재한다는 의미
	}
	
	public String existCategory(String category) {//있는 카테고리인지 확인
		String list_no = null;
		String sql = "SELECT * FROM sql WHERE category like '"+category+"'";
		Log.d("print", sql);
		Cursor cursor = getWritableDatabase().rawQuery(sql,null);

		if(cursor.getCount() > 0){
			cursor.moveToFirst();
			int n_list_no = cursor.getColumnIndex("list_no");
			list_no  = cursor.getString(n_list_no);
		}
		
		return list_no;//1개 이상의 결과값이 나온다는 말은 그 쿼리가 이미 존재한다는 의미
	}
	
	public String makeList_no(String big){
		StringBuffer sb = new StringBuffer();
		int max = 0;
		
		Cursor cursor = bigListno(big);
		if (cursor.moveToFirst()) {
			int n_list_no = cursor.getColumnIndex("list_no");
			do {
				String list_no = cursor.getString(n_list_no);
				if(Integer.parseInt(list_no) > max){
					max = Integer.parseInt(list_no);
				}
			} while (cursor.moveToNext());
		}
		sb.append(max+1);
		Log.d("PRINT", "max: " + max);
		return sb.toString();
	}
	
	public Cursor bigListno(String big) {//대분류 카테고리로 뽑아낸 listno
		String sql = "SELECT list_no FROM sql WHERE list_no like '"+big+"%'";
		Cursor cursor = getWritableDatabase().rawQuery(sql,null);		
		return cursor;
	}
	
	
}